package org.barman.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.barman.exception.PersistenceException;
import org.barman.exception.ValidationException;
import org.barman.pojo.User;
import org.barman.pojo.UserRole;
import org.barman.util.DbHelper;

public class UserDAO implements DAO<User> {

	@Override
	public void insert(User user) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection.prepareStatement(""
					+ "insert into user(username, password, email, first_name, last_name, role, created)"
					+ "values(?, ?, ?, ?, ?, ?, ?)");
			preparedStatement.setString(1, user.getUsername());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setString(3, user.getEmail());
			preparedStatement.setString(4, user.getFirstName());
			preparedStatement.setString(5, user.getLastName());
			preparedStatement.setString(6, user.getRole().getName());
			preparedStatement.setDate(7, user.getCreated());

			if (preparedStatement.executeUpdate() == 0) {
				throw new PersistenceException("username already taken");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("username already taken", e.getMessage(), e);
		}
	}

	@Override
	public void update(User user) throws PersistenceException, ValidationException {

		if (user.getId() == null) {
			throw new ValidationException("USER ID NOT SET");
		}

		try {

			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("update user set username = ?, password = ?, email = ?, first_name = ?, last_name = ?, role = ?, created = ?, modified = ? where id = ?");
			preparedStatement.setString(1, user.getUsername());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setString(3, user.getEmail());
			preparedStatement.setString(4, user.getFirstName());
			preparedStatement.setString(5, user.getLastName());
			preparedStatement.setString(6, user.getRole().getName());
			preparedStatement.setDate(7, user.getCreated());
			preparedStatement.setDate(8, user.getModified());
			preparedStatement.setInt(9, user.getId());

			// TODO update user-order references in order table

			if (preparedStatement.executeUpdate() == 0) {
				throw new PersistenceException("UPDATE USER ERROR.");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("UPDATE USER ERROR.", e.getMessage(), e);
		}
	}

	@Override
	public User get(int userId) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, username, password, email, first_name, last_name, role, created, modified from user where id = ?");
			preparedStatement.setInt(1, userId);
			ResultSet resultSet = preparedStatement.executeQuery();

			User user = new User();
			while (resultSet.next()) {
				user.setId(resultSet.getInt(1));
				user.setUsername(resultSet.getString(2));
				user.setPassword(resultSet.getString(3));
				user.setEmail(resultSet.getString(4));
				user.setFirstName(resultSet.getString(5));
				user.setLastName(resultSet.getString(6));
				user.setRole(UserRole.getRole(resultSet.getString(7)));
				user.setCreated(resultSet.getDate(8));
				user.setModified(resultSet.getDate(9));
			}

			return user;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET USER ERROR.", e.getMessage(), e);
		}
	}

	public User getByUsernameAndPassword(String username, String pass) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, username, password, email, first_name, last_name, "
							+ "role, created, modified from user where username='" + username + "' and password='"
							+ pass + "'");

			ResultSet resultSet = preparedStatement.executeQuery();

			User user = null;
			while (resultSet.next()) {
				user = new User();
				user.setId(resultSet.getInt(1));
				user.setUsername(resultSet.getString(2));
				user.setPassword(resultSet.getString(3));
				user.setEmail(resultSet.getString(4));
				user.setFirstName(resultSet.getString(5));
				user.setLastName(resultSet.getString(6));
				user.setRole(UserRole.getRole(resultSet.getString(7)));
				user.setCreated(resultSet.getDate(8));
				user.setModified(resultSet.getDate(9));
			}

			return user;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET USER BY USERNAME AND PASSWORD ERROR.", e.getMessage(), e);
		}
	}

	@Override
	public List<User> getAll() throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, username, password, email, first_name, last_name, role, created, modified from user");
			ResultSet resultSet = preparedStatement.executeQuery();

			List<User> allUsers = new ArrayList<User>();
			while (resultSet.next()) {

				User user = new User();
				user.setId(resultSet.getInt(1));
				user.setUsername(resultSet.getString(2));
				user.setPassword(resultSet.getString(3));
				user.setEmail(resultSet.getString(4));
				user.setFirstName(resultSet.getString(5));
				user.setLastName(resultSet.getString(6));
				user.setRole(UserRole.getRole(resultSet.getString(7)));
				user.setCreated(resultSet.getDate(8));
				user.setModified(resultSet.getDate(9));

				allUsers.add(user);
			}

			return allUsers;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET ALL USERS ERROR.", e.getMessage(), e);
		}
	}

	@Override
	public void delete(int userId) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection.prepareStatement("delete from user where id = ?");
			preparedStatement.setInt(1, userId);

			if (preparedStatement.executeUpdate() == 0) {
				throw new PersistenceException("DELETE USER ERROR.");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("DELETE USER ERROR.", e.getMessage(), e);
		}
	}
}
